/*NOTE: This is model code to illustrate the logic used to generate the datasets for analysis in the SCARP pilot.
This code is not intended to run as is - due to Epic restrictions on the sharing of intellectual property all 
of the table and attribute names have been "masked" with names enclosed in square brackets if they were pulled 
from Clarity (EPIC).*/

/*****************************************************
PROJECT:  BRIDGE C2
PILOT: SCRAP
DATE: 8/xx/2022
9/6/2022 Correction made:
  from if screen_date<'29FEB2020'd then delete;
	to if screen_date>'29FEB2020'd then delete;


DESCRIPTION: Use ASCEND approach to compare CRC, CVC and 
BC cancer prevention services in patients with
	No SDH Screening
	Documented Need
	Documented No Need
Stratified by SDH need: Food, Housing, Transportation
STUDY PERIOD: 7/1/2016-2/29/2020		

DETAILS (this syntax): 
Create files of order dates and screen dates

Output file: 
SCRAP.CVC_analysis_09122023
SCRAP.PAP_screen_dates



*****************************************************/;
options compress=yes reuse=yes;
libname RD odbc noprompt="dsn=research_dev;Trusted_Connection=yes" bulkload=yes;
libname SCRAP "e:\\sasroot\BRIDGE C2\SCRAP\";

proc sql;
create table rd.jpo_cvc_cohort as select* from scrap.scrap_CVC_patients;

/*Development of scarp.cvc_screens_lu file is internal - provided for reference*/
proc sql; create table RD.jpo_CVC_screens_lu as select * from scrap.CVC_screens_LU; quit;


/*pull from surgical history*/
proc sql;
connect to odbc as mycon
(required = "dsn=research_dev;Trusted_Connection=yes");
create table CVC_shx as 
select * from connection to mycon (
     SELECT den.raw_patid	
  	,shx.[contact date] 
	,shx.[surgical history start date]
     ,shx.[surgical history date] 
     ,cpc.screen
    FROM research_dev.dbo.jpo_CVC_cohort  AS den
     INNER JOIN clarity.dbo.[SURGICAL HISTORY]        AS shx
      ON den.raw_patID = shx.[PATIENT IDENTIFIER]
     INNER JOIN research_dev.dbo.jpo_CVC_screens_lu AS cpc
      ON shx.[procedure identifier] = cpc.proc_id
	;      
quit;
/*only cases where the exact date of the surgical procedure is known*/*/;


data CVC_SHX2 ;
	set CVC_SHX;
	length source $20;
	Source='Surgical Hx';
	month=0;
	day=0;
	format screen_date date9.;
	if [surgical history start date]^=. then
		do;
			screen_date=datepart([surgical history start date]);
			if screen_date>='01JUL2011'd and screen_date<='29FEB2020'd then 
				output CVC_SHX2;
		end;
	if [surgical history start date]=. then
		do;
			if [surgical history date]^=' ' and [surgical history date]^='NO REPORT' then
				do;
					if index([surgical history date],'/')^=0 then
						do;
							month=input (scan([surgical history date],1,'/'),best4.);
							day=input (scan([surgical history date],2,'/'),best4.);
							year=input (scan([surgical history date],3,'/'),best4.);
						end;
					if index([surgical history date],'-')^=0 then
						do;
							month=input (scan([surgical history date],1,'-'),best4.);
							day=input (scan([surgical history date],2,'-'),best4.);
							year=input (scan([surgical history date],3,'-'),best4.);
						end;
					if month>0 and month<13 and
						day>0 and day<32 and
						year>2005 and year<2022 then
							do;
								screen_date=mdy(month, day, year);
							end;
			if screen_date>='01JUL2011'd and screen_date<='29FEB2020'd then 
				output CVC_SHX2;
				end;
		end;
run;


*screening orders and results from [ORDER PROCEDURE];
proc sql;
connect to odbc as mycon
(required = "dsn=research_dev;Trusted_Connection=yes");
create table CVC_orders as 
select * from connection to mycon (

SELECT distinct
	den.raw_patid
    ,op.[ordering date]
	,op.[result time]
	,op.[procedure identifier]
	,op.[order status]
	,ppc.screen
	,op.[pending order yes/no]
	,op.[order procedure identifier]
	,op.[abnormal yes/no]
	,op.[display name]
	,op.[ordering date]
	,op.[result time] 	
FROM
    research_dev.dbo.jpo_CVC_cohort  AS den
		inner join Clarity.dbo.[ORDER PROCEDURE] as op
			on den.raw_patid = op.[PATIENT IDENTIFIER] 
        inner join research_dev.dbo.jpo_CVC_screens_lu AS ppc
      ON op.[procedure identifier] = ppc.proc_id);
quit;
*3=resulted 5=completed;
*2=sent ;
*4=canceled ;

data CVC_orders2 ;
set CVC_orders;
length source $20;
Source='Order proc';
format order_date date9.;
order_date=datepart([ordering date]);
where [order status]^=4;
if order_date<'01JUL2011'd then delete;
if order_date>'29FEB2020'd then delete;
run;

data CVC_screen_procs;
set CVC_orders;
format screen_date date9.;
screen_date=datepart([result time]);
where [result time]^=.;
if screen_date<'01JUL2011'd then delete;
if screen_date>'29FEB2020'd then delete;
run;


proc sql;
connect to odbc as mycon (required='dsn=research_dev;Trusted_Connection=yes');                                                             
create table clarity_LAB_components as select *
from connection to mycon (
select distinct pt.raw_patid,  
	r.[component IDENTIFIER], 	
	zr.[result_status], 
	zo.[order_status],
	r.[order procedure identifier], 
	e.[procedure code], 
	e.[procedure name], 
	o.[ordering date], 
	r.[result time], 
	r.[order value], 
	r.[order number value], 
	r.[result flag], 
	zc.[result flag name],
	o.[patient encounter contact serial number identifier], 
	cc.[default lnc identifier],
	cc.[external name],
	ppc.screen
from CLARITY.dbo.[ORDER RESULTS] r
inner join CLARITY.dbo.[ORDER PROCEDURE] o on o.[order procedure identifier]=r.[order procedure identifier]
inner join research_dev.dbo.jpo_CVC_cohort  pt on pt.raw_patid=o.[PATIENT IDENTIFIER]
left join CLARITY.dbo.[LAB STATUS] zr on zr.[lab status]=r.[lab status]
left join CLARITY.dbo.[ORDER STATUS] zo on zo.[order status]=o.[order status] 
left join clarity.dbo.[result flag] zc on r.[result flag] = zc.[result flag]
left join CLARITY.dbo.[procedures records EAP] e on e.[procedure identifier]=o.[procedure identifier]
left join clarity.dbo.[component] cc on cc.[component IDENTIFIER]=r.[component IDENTIFIER]
inner join research_dev.dbo.jpo_CVC_screens_lu AS ppc
on ppc.[component IDENTIFIER]=r.[component IDENTIFIER]
where ppc.[component IDENTIFIER] is not null);


data clarity_LAB_components;
set  clarity_LAB_components; 
length source $20;
format order_date screen_date date9.;
Source='Lab Component';
order_date=datepart([ordering date]); 
screen_date=datepart([result time]);
if screen_date<'01JUL2011'd then delete;
if screen_date>'29FEB2020'd then delete;
run;


proc sql;
connect to odbc as mycon (required='dsn=research_dev;Trusted_Connection=yes');                                                             
create table clarity_LAB_LNC_cc as select *
from connection to mycon (
select distinct pt.raw_patid,  
	r.[component IDENTIFIER], 	
	zr.[result_status], 
	zo.[order_status],
	r.[order procedure identifier], 
	e.[procedure code], 
	e.[procedure name], 
	o.[ordering date], 
	r.[result time], 
	r.[order value], 
	r.[order number value], 
	r.[result flag], 
	zc.[result flag name],
	o.[patient encounter contact serial number identifier], 
	cc.[default lnc identifier],
	r.[component lnc identifier],
	ppc.screen
from CLARITY.dbo.[ORDER RESULTS] r
inner join CLARITY.dbo.[ORDER PROCEDURE] o on o.[order procedure identifier]=r.[order procedure identifier]
inner join research_dev.dbo.jpo_CVC_cohort  pt on pt.raw_patid=o.[PATIENT IDENTIFIER]
left join CLARITY.dbo.[LAB STATUS] zr on zr.[lab status]=r.[lab status]
left join CLARITY.dbo.[ORDER STATUS] zo on zo.[order status]=o.[order status] 
left join clarity.dbo.[result flag] zc on r.[result flag] = zc.[result flag]
left join CLARITY.dbo.[procedures records EAP] e on e.[procedure identifier]=o.[procedure identifier]
left join clarity.dbo.[component] cc on cc.[component IDENTIFIER]=r.[component IDENTIFIER]
inner join research_dev.dbo.jpo_CVC_screens_lu AS ppc
on ppc.LOinc_code=cc.[default lnc identifier] 
where ppc.loinc_code is not null);

data clarity_LAB_LNC_cc;
set  clarity_LAB_LNC_cc; 
length source $20;
format order_date screen_date date9.;
Source='Lab CC LNC';
order_date=datepart([ordering date]); 
screen_date=datepart([result time]);
if screen_date<'01JUL2011'd then delete;
if screen_date>'29FEB2020'd then delete;
run;

proc sql;
create table lab_LN_cc as
select * from clarity_LAB_LNC_cc
where [order procedure identifier] ^in(select distinct [order procedure identifier] from 
	clarity_LAB_components);
quit;

data lab_results;
set clarity_LAB_components lab_LN_cc;
run;

proc sql;
connect to odbc as mycon (required='dsn=research_dev;Trusted_Connection=yes');                                                             
create table clarity_LAB_LNC_or as select *
from connection to mycon (
select distinct pt.raw_patid,  
	r.[component IDENTIFIER], 	
	zr.[result_status], 
	zo.[order_status],
	r.[order procedure identifier], 
	e.[procedure code], 
	e.[procedure name], 
	o.[ordering date], 
	r.[result time], 
	r.[order value], 
	r.[order number value], 
	r.[result flag], 
	zc.name as result_flag_name,
	o.[patient encounter contact serial number identifier], 
	r.[component lnc identifier],
	ppc.screen
from CLARITY.dbo.[ORDER RESULTS] r
inner join CLARITY.dbo.[ORDER PROCEDURE] o on o.[order procedure identifier]=r.[order procedure identifier]
inner join research_dev.dbo.jpo_CVC_cohort  pt on pt.raw_patid=o.[PATIENT IDENTIFIER]
left join CLARITY.dbo.[LAB STATUS] zr on zr.[lab status]=r.[lab status]
left join CLARITY.dbo.[ORDER STATUS] zo on zo.[order status]=o.[order status] 
left join clarity.dbo.[result flag] zc on r.[result flag] = zc.[result flag]
left join CLARITY.dbo.[procedures records EAP] e on e.[procedure identifier]=o.[procedure identifier]
inner join research_dev.dbo.jpo_CVC_screens_lu AS ppc
on ppc.Loinc_code=r.[component lnc identifier] 
where ppc.loinc_code is not null);

data clarity_LAB_LNC_or;
set  clarity_LAB_LNC_or; 
length source $20;
format order_date screen_date date9.;
Source='Lab OR LNC';
order_date=datepart([ordering date]); 
screen_date=datepart([result time]);
if screen_date<'01JUL2011'd then delete;
if screen_date>'29FEB2020'd then delete;
run;

proc sql;
create table lab_LN_OR as
select * from clarity_LAB_LNC_cc
where [order procedure identifier] ^in(select distinct [order procedure identifier] from 
	lab_results);
quit;

/*drop records already included in CVC_screen procs*/
proc sql;
create table new_results as
select * from lab_results
where [order procedure identifier] ^in
(select distinct [order procedure identifier] from CVC_screen_procs);


data ordered_screens;
set CVC_screen_procs new_results;
run;

data screen_check;
set ordered_screens;
where screen_date<order_date-60;
run;

data ordered_screens;
set ordered_screens;
if screen_date<order_date-60 then delete;
run;

/*Screens from HM_history*/
proc sql;
connect to odbc as mycon
(required = "dsn=OCHIN_HCN_FENWAY;Trusted_Connection=yes");
create table HMT_hx as select *
from connection to mycon (
SELECT 
	p.raw_patid
    ,hmt.[NAME] as HMT_name
	,hmo.[HEALTH MAINTENANCE TOPIC IDENTIFIER]
    ,hmo.[HEALTH MAINTENANCE HISTORY DATE]
FROM 
	Clarity.dbo.[HEALTH MAINTENANCE HISTORY] hmo
		INNER JOIN research_dev.dbo.jpo_CVC_cohort p
			ON hmo.[PATIENT IDENTIFIER] = p.raw_PATID
		LEFT JOIN Clarity.dbo.[HEALTH MAINTENANCE TOPIC] hmt
			ON hmt.[HEALTH MAINTENANCE TOPIC IDENTIFIER] = hmo.[HEALTH MAINTENANCE TOPIC IDENTIFIER]
WHERE 
	
		(hmo.[HEALTH MAINTENANCE TOPIC IDENTIFIER] between 1 and 5 or hmo.[HEALTH MAINTENANCE TOPIC IDENTIFIER] IN (18,34,57)) 
	and hmo.HM_TYPE_C = 1 );

data HMT_HX;
set HMT_HX;
length source $20 screen $5;
format screen_date date9.;
screen_date=datepart([HEALTH MAINTENANCE HISTORY DATE]);
Source='HMT HX';
if screen_date<'01JUL2011'd then delete;
if screen_date>'29FEB2020'd then delete;
if [HEALTH MAINTENANCE TOPIC IDENTIFIER] in(1,2,3,4,5,18) then screen='PAP';
if [HEALTH MAINTENANCE TOPIC IDENTIFIER] in(34,57) then screen='COTEST';
run;


/*combine orders*/
data orders;
set CVC_orders new_results;
run;

proc sql;
select count([order procedure identifier]), count(distinct [order procedure identifier]) from orders;
*deduplicate;
proc sql;
create table orders2  as
select raw_patid,
screen,
order_date,
[order procedure identifier],
count([order procedure identifier]) as records,
count(distinct source) as sources
from orders
group by raw_patid,
screen,
order_date,
[order procedure identifier];

data scrap.CVC_order_dates;
set orders2;
run;
*limit orders to pap;
data scrap.PAP_order_dates;
set scrap.CVC_order_dates;
where screen='PAP';
run;

/* combine screens */
data screens;
set CVC_screen_procs new_results CVC_shx2 HMT_HX;
run;

/*deduplicate*/
proc sql;
create table CVC_screen_dates as
select raw_patid,
screen,
screen_date,
sum(source='HMT HX') as HMT_HX_records,
sum(source in('Lab CC LNC','Lab Component')) as Lab_re_records,
sum(source='Order proc') as ord_proc_records,
sum(source='Surgical Hx') as Surg_hx_records
from screens
group by raw_patid,
screen,
screen_date;

data pap HPV cotest;
set CVC_screen_dates;
where screen_date^=.;
if screen="COTES" then output COTEST;
if screen="PAP" then output PAP;
if screen="HPV" then output HPV;
run;

*merge pap to HPV;
/*UDS 2020: HPV must be co-test with PAP*/
proc sql;
create table pap_HPV as
select p.*,
h.screen_date as HPV_date format date9.
from PAP p left join
HPV h
on p.raw_patid=h.raw_patid and 
h.screen_date >= p.screen_date-1 and h.screen_date <= p.screen_date+1;

data cotest;
set cotest;
format hpv_date date9.;
HPV_date=screen_date;
run;

data pap_screen_dates;
set pap_HPV cotest;
run;

/*add age at screen_date*/
proc sql;
create table pap_screen_dates2 as
select p.*,
d.sas_birth_date format date9.
from pap_screen_dates p 
left join ochin.demographic d
on p.raw_patid=d.raw_patid;

/*add next due*/

data SCRAP.PAP_screen_dates;
set pap_screen_dates2;
age_at_screen=yrdif(sas_birth_date,screen_date,'age');
format next_due date9.;
if age_at_screen<21 then delete;
if age_at_screen<30 then do;
	next_due=intnx('year',screen_date,3,'same');
end;
if age_at_screen>=30 then do;
	if HPV_date=. then next_due=intnx('year',screen_date,3,'same');
	if HPV_date^=. then next_due=intnx('year',screen_date,5,'same');
end;
run;
